package com.Spring.SpringSpms.dao;

import java.util.List;

import com.Spring.SpringSpms.model.Status;


import java.sql.ResultSet;
import java.sql.SQLException;


import javax.sql.DataSource;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;


public class StatusDAOImpl implements StatusDAO{

	private JdbcTemplate jdbcTemplate;
	public StatusDAOImpl(DataSource dataSource) {
		// TODO Auto-generated constructor stub
		jdbcTemplate = new JdbcTemplate(dataSource);
	}
	@Override
	public List<Status> list() {
		
	

		String sql = "SELECT * FROM Status";
		List<Status> listStatus = jdbcTemplate.query(sql, new RowMapper<Status>() {

			@Override
			public Status mapRow(ResultSet rs, int rowNum) throws SQLException {
				Status aStatus = new Status();
	
				aStatus.setStatus_id(rs.getInt("Status_id"));
				aStatus.setStatus_name(rs.getString("status_name"));
	
			return aStatus;
			}
		});
		return listStatus;
}
	@Override
	public void saveOrUpdate(Status s) {
		// TODO Auto-generated method stub

		
		if (s.getStatus_id() > 0) {
			// update
			String sql = "UPDATE Status SET status_name=? WHERE status_id=?";
			jdbcTemplate.update(sql, s.getStatus_name());
		} else {
			// insert
			String sql = "INSERT INTO Status (status_name)"
						+ " VALUES (?)";
			jdbcTemplate.update(sql, s.getStatus_name());
			}

		
	}
	@Override
	public void delete(int statustId) {
		// TODO Auto-generated method stub

		String sql = "DELETE FROM Status WHERE status_id=?";
		jdbcTemplate.update(sql, statustId);
		
	}
	@Override
	public Status get(int statusId) {
		
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM Status WHERE status_id=" + statusId;
		return jdbcTemplate.query(sql, new ResultSetExtractor<Status>() {

			@Override
			public Status extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				if (rs.next()) {
					Status s = new Status();
					s.setStatus_id(rs.getInt("status_id"));
					s.setStatus_name(rs.getString("status_name"));
					return s;
				}
				
				return null;
			}
			
		});

	}
	@Override
	public List<String> listProjectStatus(int empid) {
		
		String sql="select Status.status_name from Status,Project,Employee_Project where Status.status_id=Project.status_id and Employee_Project.emp_id=? and Employee_Project.project_id=Project.project_id order by Project.project_id";
		List<String> statuslist=(List<String>) jdbcTemplate.queryForList(sql,String.class,empid);		
		return statuslist;
	}

}